/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Ticket;

/**
 *
 * @author 03100286
 */
public class TicketDao {
    public boolean insere(Ticket ticket) throws SQLException,UsuarioDuplicadoException {
        String sql = "INSERT INTO tickets(assunto,descricao_ticket,id_usuario) VALUES(?,?,?)";
        bancoCasehd banco = new bancoCasehd();
        Connection con = (Connection) banco.getConexao().getInnerConnection();

	try {
            // pega o usuário logado na sessão
            PreparedStatement ptm = (PreparedStatement) con.prepareStatement(sql);
            ptm.setString(1, ticket.getAssunto());
            ptm.setString(2, ticket.getDescricao_ticket());
            ptm.setInt(3, ticket.getId_usuario());
            int row = ptm.executeUpdate();
            if(row == 1) {
                // antes de retornar guarda o ID gerado para o ticket
                ticket.setId_ticket( getUltimoId() );
                return true;
            }
	} catch (SQLException e) {
           
	}
        return false;
    }

    public ArrayList lista() throws SQLException{
        Ticket ticket  = null;
        ArrayList listTicket = new ArrayList();
        String sql = "select tk.`id_ticket`, tk.`assunto`, tk.`descricao_ticket`, tk.`id_usuario`, tk.`status`, "
                   + "       u.`nome` as nome_usuario "
                   + "from tickets tk "
                   + "join `usuarios` u on (u.`id` = tk.`id_usuario`)";

	bancoCasehd banco = new bancoCasehd();
        java.sql.Connection con = banco.getConexao().getInnerConnection();

        Statement stmt = null;
        ResultSet rs = null;
        stmt = (Statement) con.createStatement();
        rs  = stmt.executeQuery(sql);
        while(rs.next()){
            ticket = new Ticket();
            ticket.setAssunto(rs.getString("assunto"));
            ticket.setDescricao_ticket(rs.getString("descricao_ticket"));                        
            ticket.setId_usuario(rs.getInt("id_usuario"));
            ticket.setId_ticket(rs.getInt("id_ticket"));
            ticket.setStatus(rs.getInt("status"));
            ticket.setNome_usuario(rs.getString("nome_usuario"));
            listTicket.add(ticket);
        }
        return listTicket;
    }

    public Ticket buscaPorID(Integer id) throws SQLException{
        Ticket ticket  = new  Ticket();
        String sql = "select tk.`id_ticket`, tk.`assunto`, tk.`descricao_ticket`, tk.`id_usuario`, tk.`status`, "
                   + "       u.`nome` as nome_usuario "
                   + "from tickets tk "
                   + "join `usuarios` u on (u.`id` = tk.`id_usuario`)"
                   + "where id_ticket = ? ";
   	bancoCasehd banco = new bancoCasehd();
        java.sql.Connection con = banco.getConexao().getInnerConnection();

        PreparedStatement ptm = (PreparedStatement) con.prepareStatement(sql);
        ptm.setInt(1, id);
        ResultSet rs = null;
        rs  = ptm.executeQuery();
       
        while(rs.next()){
            ticket.setAssunto(rs.getString("assunto"));
            ticket.setDescricao_ticket(rs.getString("descricao_ticket"));
            ticket.setId_usuario(rs.getInt("id_usuario"));
            ticket.setId_ticket(rs.getInt("id_ticket"));
            ticket.setStatus(rs.getInt("status"));
            ticket.setNome_usuario(rs.getString("nome_usuario"));
        }
        return ticket;
    }

    private int getUltimoId() throws SQLException {
        String sql = "select LAST_INSERT_ID(id_ticket) as ultimo from tickets order by id_ticket desc limit 1";
   	bancoCasehd banco = new bancoCasehd();
        java.sql.Connection con = banco.getConexao().getInnerConnection();

        Statement stmt = null;
        ResultSet rs = null;
        stmt = (Statement) con.createStatement();
        rs  = stmt.executeQuery(sql);
        while(rs.next()){
            return rs.getInt("ultimo");
        }
        return -1;
    }

}
